﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Reflection;
using System.Diagnostics;

namespace System.Helper
{
    /// <summary>
    /// Excel操作类
    /// 依赖于Excel但不具体依赖于Excel的具体版本
    /// 导出的Excel是标准的Excel格式，可以直接拿来进行输入导入使用
    /// 除非有特殊需求，需要用导出的Excel进行数据导入
    /// 否则不建议使用此类（主要是生成、导出Excel性能比较低）
    /// </summary>
    public partial class ExcelHelper : System.IDisposable
    {
        private static readonly string[] table = { "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z", "AA", "AB", "AC", "AD", "AE", "AF", "AG", "AH", "AI", "AJ", "AK", "AL", "AM", "AN", "AO", "AP", "AQ", "AR", "AS", "AT", "AU", "AV", "AW", "AX", "AY", "AZ", "BA", "BB", "BC", "BD", "BE", "BF", "BG", "BH", "BI", "BJ", "BK", "BL", "BM", "BN", "BO", "BP", "BQ", "BR", "BS", "BT", "BU", "BV", "BW", "BX", "BY", "BZ", "CA", "CB", "CC", "CD", "CE", "CF", "CG", "CH", "CI", "CJ", "CK", "CL", "CM", "CN", "CO", "CP", "CQ", "CR", "CS", "CT", "CU", "CV", "CW", "CX", "CY", "CZ", "DA", "DB", "DC", "DD", "DE", "DF", "DG", "DH", "DI", "DJ", "DK", "DL", "DM", "DN", "DO", "DP", "DQ", "DR", "DS", "DT", "DU", "DV", "DW", "DX", "DY", "DZ", "EA", "EB", "EC", "ED", "EE", "EF", "EG", "EH", "EI", "EJ", "EK", "EL", "EM", "EN", "EO", "EP", "EQ", "ER", "ES", "ET", "EU", "EV", "EW", "EX", "EY", "EZ", "FA", "FB", "FC", "FD", "FE", "FF", "FG", "FH", "FI", "FJ", "FK", "FL", "FM", "FN", "FO", "FP", "FQ", "FR", "FS", "FT", "FU", "FV", "FW", "FX", "FY", "FZ", "GA", "GB", "GC", "GD", "GE", "GF", "GG", "GH", "GI", "GJ", "GK", "GL", "GM", "GN", "GO", "GP", "GQ", "GR", "GS", "GT", "GU", "GV", "GW", "GX", "GY", "GZ", "HA", "HB", "HC", "HD", "HE", "HF", "HG", "HH", "HI", "HJ", "HK", "HL", "HM", "HN", "HO", "HP", "HQ", "HR", "HS", "HT", "HU", "HV", "HW", "HX", "HY", "HZ", "IA", "IB", "IC", "ID", "IE", "IF", "IG", "IH", "II", "IJ", "IK", "IL", "IM", "IN", "IO", "IP", "IQ", "IR", "IS", "IT", "IU", "IV" };

        private object missing = Type.Missing;
        object workbooks;
        object excel;
        object workbook;
        object worksheet;
        /// <summary>
        /// 初始化新的工作簿，用于生成新的Excel文件
        /// </summary>
        public ExcelHelper()
        {
            Init();
        }

        /// <summary>
        /// 打开指定Excel文件，用于数据读取
        /// </summary>
        /// <param name="fPath"></param>
        public ExcelHelper(string fPath)
        {
            Init();
            Open(fPath);
        }

        /// <summary>
        /// 初始化变量，自动添加新的工作簿
        /// </summary>
        private void Init()
        {
            Type excelType = Type.GetTypeFromProgID("Excel.Application");
            this.excel = Activator.CreateInstance(excelType);
            excelType.InvokeMember("Visible", BindingFlags.SetProperty, null, excel, new object[] { true });
            workbooks = this.excel.GetType().InvokeMember("Workbooks", BindingFlags.GetProperty, null, excel, null);
            this.workbook = workbooks.GetType().InvokeMember("Add", BindingFlags.InvokeMethod, null, workbooks, new object[] { true });
            worksheet = workbook.GetType().InvokeMember("ActiveSheet", BindingFlags.GetProperty, null, workbook, null);
        }
        /// <summary>
        /// 获取单元格
        /// </summary>
        /// <param name="row"></param>
        /// <param name="col"></param>
        /// <returns></returns>
        private object GetCell(int row, int col)
        {
            object cell = worksheet.GetType().InvokeMember("Cells", BindingFlags.GetProperty, null, worksheet, new object[] { row, col });
            return cell;
        }

        /// <summary>
        /// 打开已有Excel文件，默认激活第一个工作簿
        /// </summary>
        /// <param name="fPath"></param>
        public void Open(string fPath)
        {
            object[] param = new object[] { fPath, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing };
            this.workbook = workbooks.GetType().InvokeMember("Open", BindingFlags.InvokeMethod, null, workbooks, param);
            worksheet = workbook.GetType().InvokeMember("Worksheets", BindingFlags.GetProperty, null, workbook, new object[] { 1 });
        }

        /// <summary>
        /// 填充指定单元格
        /// </summary>
        /// <param name="row"></param>
        /// <param name="col"></param>
        /// <param name="val"></param>
        public void FillCell(int row, int col, object val)
        {
            object cell = GetCell(row, col);
            cell.GetType().InvokeMember("FormulaR1C1", BindingFlags.SetProperty, null, cell, new object[] { val });
        }

        /// <summary>
        /// 填充指定单元格
        /// </summary>
        /// <param name="row"></param>
        /// <param name="col"></param>
        /// <param name="val"></param>
        public void FillCell(int row, string col, object val)
        {
            int i = 0;
            for (; i < table.Length; i++)
            {
                if (table[i] == col)
                {
                    break;
                }
            }
            FillCell(row, i + 1, val);
        }

        /// <summary>
        /// 获取指定单元格的文本值
        /// </summary>
        /// <param name="row"></param>
        /// <param name="col"></param>
        /// <returns></returns>
        public string GetCellText(int row, int col)
        {
            object cell = GetCell(row, col);
            return cell.GetType().InvokeMember("Text", BindingFlags.GetProperty, null, cell, null).ToString();
        }

        /// <summary>
        /// 将文件保存到指定路径
        /// </summary>
        /// <param name="fPath"></param>
        public void Save(string fPath)
        {
            object[] args = new object[] { fPath, missing, missing, missing, missing, missing, 1, missing, missing, missing, missing, missing };
            this.workbook.GetType().InvokeMember("SaveAs", BindingFlags.InvokeMethod, null, this.workbook, args);
            this.Dispose();
        }

        /// <summary>
        /// 对工作簿进行密码写保护
        /// </summary>
        /// <param name="password"></param>
        public void ProtectedWorkSheet(string password)
        {
            object[] args = new object[] { password, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing };
            this.worksheet.GetType().InvokeMember("Protect", BindingFlags.InvokeMethod, null, this.worksheet, args);
        }

        /// <summary>
        /// 对Excel文件进行密码写保护
        /// </summary>
        /// <param name="password"></param>
        public void ProtectedWorkBook(string password)
        {
            object[] args = new object[] { password, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing };
            this.workbook.GetType().InvokeMember("Protect", BindingFlags.InvokeMethod, null, this.workbook, args);
        }

        /// <summary>
        /// 设置工作簿名称
        /// </summary>
        /// <param name="sname"></param>
        public void SetWorkSheetName(string name)
        {
            this.worksheet.GetType().InvokeMember("Name", BindingFlags.SetProperty, null, this.worksheet, new object[] { name });
        }

        /// <summary>
        /// 设置单元格属性值
        /// </summary>
        /// <param name="row"></param>
        /// <param name="col"></param>
        /// <param name="PropertyName"></param>
        /// <param name="PropertyValue"></param>
        public void SetCellPropertyValue(int row, int col, string PropertyName, string PropertyValue)
        {
            object cell = GetCell(row, col);
            cell.GetType().InvokeMember(PropertyName, BindingFlags.SetProperty, null, cell, new object[] { PropertyValue });
        }

        /// <summary>
        /// 设置单元格背景颜色
        /// </summary>
        /// <param name="row"></param>
        /// <param name="col"></param>
        /// <param name="color"></param>
        private void SetCellColor(int row, int col, string color)
        {
            object cell = GetCell(row, col);//Cells.Interior.Color
            object cells = cell.GetType().InvokeMember("Cells", BindingFlags.GetProperty, null, cell, null);
            object interior = cells.GetType().InvokeMember("Interior", BindingFlags.GetProperty, null, cells, null);
            interior.GetType().InvokeMember("Color", BindingFlags.SetProperty, null, interior, new object[] { color });
        }

        /// <summary>
        /// 批量设置单元格背景颜色
        /// </summary>
        /// <param name="beginRow"></param>
        /// <param name="beginCol"></param>
        /// <param name="endRow"></param>
        /// <param name="endCol"></param>
        /// <param name="color"></param>
        public void SetCellColor(int beginRow, int beginCol, int endRow, int endCol, string color)
        {
            for (int row = beginRow; row <= endRow; row++)
            {
                for (int col = beginCol; col <= endCol; col++)
                {
                    SetCellColor(row, col, color);
                }
            }
        }

        /// <summary>
        /// 设置单元格自动适应列宽
        /// </summary>
        /// <param name="row"></param>
        /// <param name="col"></param>
        public void SetCellAutoFit(int row, int col)
        {
            object cell = GetCell(row, col);
            object entireColumn = cell.GetType().InvokeMember("EntireColumn", BindingFlags.GetProperty, null, cell, null);
            entireColumn.GetType().InvokeMember("AutoFit", BindingFlags.InvokeMethod, null, entireColumn, null);

        }

        /// <summary>
        /// 批量设置单元格自动适应列宽
        /// </summary>
        /// <param name="beginRow"></param>
        /// <param name="beginCol"></param>
        /// <param name="endRow"></param>
        /// <param name="endCol"></param>
        public void SetCellAutoFit(int beginRow, int beginCol, int endRow, int endCol)
        {
            for (int row = beginRow; row <= endRow; row++)
            {
                for (int col = beginCol; col <= endCol; col++)
                {
                    SetCellAutoFit(row, col);
                }
            }
        }

        /// <summary>
        /// 单元格格式设置
        /// </summary>
        /// <param name="row"></param>
        /// <param name="col"></param>
        /// <param name="format"></param>
        public void SetCellFormat(int row, int col, string format)
        {
            //object cell = GetCell(row, col);
            //cell.GetType().InvokeMember("NumberFormat", BindingFlags.SetProperty, null, cell, new object[] { format });
            SetCellPropertyValue(row, col, "NumberFormat", format);
        }

        public void SetCellFormat(int beginRow, int beginCol, int endRow, int endCol, string format)
        {
            for (int row = beginRow; row <= endRow; row++)
            {
                for (int col = beginCol; col <= endCol; col++)
                {
                    SetCellFormat(row, col, format);
                }
            }
        }

        public void SetCellFormatText(int row, int col)
        {
            SetCellFormat(row, col, "@");
        }

        #region IDisposable 成员

        public void Dispose()
        {
            this.excel.GetType().InvokeMember("Quit", BindingFlags.InvokeMethod, null, excel, null);
            GC.Collect();
            //foreach (Process myProcess in Process.GetProcessesByName("EXCEL"))
            //{
            //    myProcess.Kill();
            //}
            //System.Threading.Thread.Sleep(1);
        }

        #endregion

       
    }
}
